{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "MUS2019-CTF.ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/google/timesketch/blob/master/notebooks/MUS2019_CTF.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2y1Dij2Z7C4n"
      },
      "source": [
        "# Magnet User Summit CTF 2019\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V3DWqc_275Jm"
      },
      "source": [
        "The folks at [Magnet Forensics](https://www.magnetforensics.com/) had a [conference](https://magnetusersummit.com/) recently, and as part of it they put together a digital forensics-themed Capture the Flag competition. I wasn't able to attend, but thankfully they [released the CTF online](https://www.hecfblog.com/2019/04/daily-blog-657-mus2019-dfir-ctf-open-to.html) a few days after the live competition ended. \n",
        "\n",
        "It looked like a lot of fun and I wanted to take a crack at it using the open source tools we use/build here at Google. \n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1_pWpmTdnYJM"
      },
      "source": [
        "## Forensics Preprocessing"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2Hph_KETIvOT"
      },
      "source": [
        "I'm going to focus on how to find the answers to the CTF questions after all the processing has been done. I'll quickly summarize the processing steps I did to get to the state when I pick up my walkthrough.\n",
        "\n",
        "I started off by processing the provided E01 image with a basic log2timeline command; nothing special added:\n",
        "\n",
        "\n",
        "```\n",
        "log2timeline.py MUS2019-CTF.plaso MUS-CTF-19-DESKTOP-001.E01\n",
        "```\n",
        "\n",
        "Once that finished, I went to [Timesketch](https://github.com/google/timesketch), made a new sketch, and uploaded the MUS2019-CTF.plaso file I just made. The *.plaso* file is a database containing the results of my log2timeline run; Timesketch can read it and provide a nice, collaborative interface for reviewing and exploring that data.\n",
        "\n",
        "Most of what I'm going to show you is done in Colab by accessing the Timesketch API in Python. You can do most of the steps in the Timesketch web interface directly, but I wanted to demonstrate how you can use Python, Colab, Timesketch, and Plaso together to work a case. \n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w-UPKnDLnbBF"
      },
      "source": [
        "## Timesketch & Colab Setup\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0dmFr3P_9Ao7"
      },
      "source": [
        "First, if you want to run this notebook and play along, click the 'Connect' button at the top right of the page. The [Timesketch GitHub](https://github.com/google/timesketch) has Colab ([Timesketch and Colab](https://colab.research.google.com/github/google/timesketch/blob/master/notebooks/colab-timesketch-demo.ipynb)) that walks through how to install, connect, and explore a Sketch using Colab. Please check it out if you want a more thorough explanation of the setup; I'm just going to show the commands you need to run to get it working: "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h35lMbAxIeYE"
      },
      "source": [
        "# Install the TimeSketch API client if you don't have it\n",
        "!pip install timesketch-api-client\n",
        "\n",
        "# Import some things we'll need\n",
        "from timesketch_api_client import config\n",
        "from timesketch_api_client import search\n",
        "import pandas as pd\n",
        "pd.options.display.max_colwidth = 60"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ph1jGHR5JjZo"
      },
      "source": [
        "### Connect to Timesketch"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i5JCNPkL87Cq"
      },
      "source": [
        "By default, this will connect to the public demo Timesketch server, which [David Cowen](https://twitter.com/HECFBlog) has graciously allowed to host a copy of the Plaso timeline of the MUS2019-CTF. Thanks Dave!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dqwwIBOpJfZi",
        "cellView": "form"
      },
      "source": [
        "#@title Client Information \n",
        "# @markdown In order to connect to Timesketch you need to first get a Timesketch object, which will require you to answer\n",
        "# @markdown some questions the first time you execute this code. The answers are:\n",
        "# @markdown + **auth_mode**: timesketch (username/pwd combination)\n",
        "# @markdown + **host_uri**: https://demo.timesketch.org\n",
        "# @markdown + **username**: demo\n",
        "# @markdown + **password**: demo\n",
        "\n",
        "ts_client = config.get_client(confirm_choices=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OZ4CahZZPjht"
      },
      "source": [
        "Now that we've connected to the Timesketch server, we need to select the Sketch that has the CTF timeline. \n",
        "\n",
        "First we'll list the available sketches, then print their names:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vsEZI45porba"
      },
      "source": [
        "sketches = ts_client.list_sketches()\n",
        "ctf = None\n",
        "for sketch in sketches:\n",
        "  print('[{0:d}] {1:s}'.format(sketch.id, sketch.name))\n",
        "  if sketch.name == 'MUS2019 CTF':\n",
        "    ctf = sketch"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E6RX0jqzQrRq"
      },
      "source": [
        "Then we'll select the MUS2019-CTF sketch (shown as sketch 3 above; you can change the number below to select a different sketch):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hvCl6L7ZQrsZ"
      },
      "source": [
        "print(ctf.name)\n",
        "print(ctf.description)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4P9vvN2KWRjX"
      },
      "source": [
        "Lastly, I'll briefly explain a few paramters of the **explore** function, which we'll use heavily when answering questions.\n",
        "\n",
        "<sketch_name>.explore() is how we send queries to Timesketch and get results back. **query_string**, **return_fields**, and **as_pandas** are the main parameters I'll be using:\n",
        " - query_string: This is the same as the query you'd enter if you were using the Timesketch web interface.\n",
        " - return_fields: Here we specify what fields we want back from Timesketch. This is where we can get really specific using Colab and only get the things we're interested in (which varies depending on what data types we're expecting back).\n",
        " - as_pandas: This just a boolen value which tells Timesketch to return a [Pandas DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html), rather than a dictionary. We'll have this set to True in all our queries, since DataFrames are awesome!\n",
        "\n",
        "Okay, enough setup. Let's get to answering questions!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tzvfE55GLKhB"
      },
      "source": [
        "# Questions\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Pfy7e0iO9MJK"
      },
      "source": [
        "![Basic - Desktop Questions](https://dfir.blog/content/images/2019/04/MUS2019-CTF-Basic-Desktop.png)\n",
        "\n",
        "I grouped the questions from the 'Basic - Desktop' section into three categories: NTFS, TeamViewer, and Registry."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SBtoNqZcp_Oi"
      },
      "source": [
        "## NTFS Questions\n",
        "This first set of questions relate to aspects of NTFS: MFT entries, sequence numbers, USN entries, and VSNs."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t4G6-lVUqN_V"
      },
      "source": [
        "As a little refresher, the 64-bit **file reference address** (or number) is made up of the **MFT entry** (48 bits) and **sequence** (16 bits) numbers. We often see this represented as something like 1234-2, with 1234 being the MFT entry number and 2 being the sequence number. Plaso calls the MFT entry number the **inode**, since that's the more generic term that applies across file systems."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aVcSxh925V2T"
      },
      "source": [
        "### Q: What is the name of the file associated with MFT entry number 102698?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "m6apzEsfAGeo"
      },
      "source": [
        "Since Plaso parses out the MFT entry (or as it calls it, inode) into its own field, let's do a query for all records with that value:\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0n6k7v-ioy4_"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'inode:102698'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,inode,filename'\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','inode','filename']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zuBoWImjAjs-"
      },
      "source": [
        "Multiple results, as is expected since Plaso creates multiple records for different types of timestamps, but they all point to the same filename: **/Users/Administrator/Downloads/TeamViewer_Setup.exe**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JY3n9O60hdf8"
      },
      "source": [
        "ts_results.filename.unique()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RPH7R0tKtLXn"
      },
      "source": [
        "### Q: What is the file name that represented MFT entry 60725 with a sequence number of 10?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lHvfLumPlC5J"
      },
      "source": [
        "The quick way to answer this is to just search for the MFT entry number (60725) and look for references to sequence number 10 in the message field:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "idzQZADYj8LF"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = '60725'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,filename,message'\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','filename','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lEAo1lLcUeBb"
      },
      "source": [
        "That's a bunch of rows, so let's filter it down by searching for messages that contain '60725-10':"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iPCYySiEVB6P"
      },
      "source": [
        "ts_results[ts_results.message.str.contains('60725-10')]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ShIpZoBRV-xi"
      },
      "source": [
        "That filename is really long and cut off; let's just select that field, then deduplicate using set():"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7xRO5-FFV_KN"
      },
      "source": [
        "set(ts_results[ts_results.message.str.contains('60725-10')].filename)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VX8kLLl4ltAI"
      },
      "source": [
        "Another way to solve this is to query for the file reference number directly. That's not as easy as it sounds, since Plaso stores it in the hex form ([I'm working on fixing that](https://github.com/log2timeline/plaso/issues/2453)). We can work with that though! \n",
        "\n",
        "Let's do the same query as above, but add the file_reference field:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qzemkUIAl-dX"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = '60725'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,file_reference,filename,message'\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','file_reference','filename','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ujdlJk_a9Hxo"
      },
      "source": [
        "The *file_reference* value is not the format we want, since it's hard to tell what the sequence number is. We can convert it to a more useful form though: "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qM4QlMgqmQRk"
      },
      "source": [
        "# Drop any rows with NaN, since they aren't what we're looking for and will \n",
        "# break the below function.\n",
        "ts_results = ts_results.dropna()\n",
        "pd.options.display.max_colwidth = 110\n",
        "\n",
        "# Replace the file_reference hex value with the human-readable MFT-Seq version. \n",
        "# This is basically what Plaso does to display the result in the 'message' \n",
        "# string we searched for. \n",
        "ts_results['file_reference'] = ts_results['file_reference'].map(\n",
        "    lambda x: '{0:d}-{1:d}'.format(int(x) & 0xffffffffffff, int(x) >> 48))\n",
        "ts_results[['datetime','timestamp_desc','data_type','file_reference','filename']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5zq9iqSJ9bA6"
      },
      "source": [
        "There. Now we have the file_reference number in an easier-to-read format, and the history of all filenames that MFT entry 60725 has had! It's easy to look for the entry with a sequence number of 10 and get our answer."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hoj0JdKQrDru"
      },
      "source": [
        "### Q: Which file name represents the USN record where the USN number is 546416480?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tDnlbF-8rfIF"
      },
      "source": [
        "Like other questions, the quick, generic way to answer is to just search for the unique detail; in this case, search in Timesketch for '546416480'. I'll show the more targeted way below, but it's pretty simple:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lPObip1NrOtn"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'update_sequence_number:546416480'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,update_sequence_number,filename'\n",
        "ts_results = search_obj.table\n",
        "ts_results.shape\n",
        "#ts_results[['datetime','timestamp_desc','data_type','update_sequence_number','filename']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TfRrngTvysQi"
      },
      "source": [
        "### Q: What is the MFT sequence number associated with the file \"\\Users\\Administrator\\Desktop\\FTK_Imager_Lite_3.1.1\\FTK Imager.exe\"?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xq0HG3CV2s1s"
      },
      "source": [
        "We'll handle this question like other ones involving the file reference address, except in this case we first need to find the MFT entry number (or inode) from the file name. Searching for the whole file path in Timesketch is problematic (slashes among other things), so let's search for the file name and then verify the path is right:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lvxp9ltq0plT"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'FTK Imager.exe'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,inode,message,filename'\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','inode','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MZ14rd0f3rCC"
      },
      "source": [
        "In the second row of the results, we can find the correct path we're looking for in the message and see that the corresponding inode is 99916. We could do another search, similar to how we answered other questions... or we could just look down a few rows for a USN entry that shows: \"FTK Imager.exe File reference: 99916-**4**\".  There's the answer!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "s3GAl6TTjTS9"
      },
      "source": [
        "ts_results[\n",
        "    ~ts_results.filename.isna() & (\n",
        "        ts_results.filename.str.contains(r'Users\\\\Administrator\\\\Desktop\\\\FTK_Imager_Lite_3.1.1\\\\FTK Imager.exe'))][['filename', 'inode']].drop_duplicates()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VjF1NkzwsROK"
      },
      "source": [
        "### Q: What is the Volume Serial Number of the Desktop's OS volume?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3d-el0ezsoQi"
      },
      "source": [
        "I know the VSN can be found in multiple places, but the first one I thought of was as part of a Prefetch file, so let's do it that way. \n",
        "\n",
        "I'll search for all 'volume creation' Prefetch records, since I don't really care about which particular one, beyond that it's from the OS drive. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1gt8_VMxsQ77"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'data_type:\"windows:volume:creation\"'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,device_path,hostname,serial_number,message'\n",
        "ts_results = search_obj.table\n",
        "\n",
        "pd.options.display.max_colwidth = 70\n",
        "ts_results[['datetime','timestamp_desc','data_type','device_path','hostname','serial_number','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KUtZWLKmuWet"
      },
      "source": [
        "You can see the VSN in a readable format at the end of the device_path or in the message string. I'm only seeing one value here, so we don't need to determine which drive was the OS one. If we did, I'd look for some system processes that need to run from the OS drive to get the right VSN. \n",
        "\n",
        "That's good enough for the question, but let's also convert the serial_number field from an integar to the hex format the answer wants, just to be sure:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NmTdhbK7kLjB"
      },
      "source": [
        "for serial_nr in ts_results.serial_number.unique():\n",
        "  print('{0:08X}'.format(serial_nr))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4Ig0sZEC8fYa"
      },
      "source": [
        "## TeamViewer Questions\n",
        "The next group of questions involved [TeamViewer](https://www.teamviewer.com/en-us/), a common remote desktop program."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qsH4fUlqIUho"
      },
      "source": [
        "### Q: Which user installed Team Viewer?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hl_qGSxrLzN6"
      },
      "source": [
        "We can start searching very broadly, then focus in on anything that stands out. Let's just search everything we have for \"TeamViewer\":"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Hs2NyOf0IUS2"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'TeamViewer'\n",
        "search_obj.return_fields='datetime,timestamp_desc,timestamp,data_type,message'\n",
        "ts_results = search_obj.table\n",
        "\n",
        "ts_results[['datetime','timestamp_desc','data_type','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ACBv7solSKa9"
      },
      "source": [
        "That returned a lot of results (600+). We could page through them all, but why not see if there are any interesting clusters first? That sounds like a job for a visualization!\n",
        "\n",
        "You can do this multiple ways; I'll do it in Python in a second, but the explanation is a bit complicated. The easier way is to do the search in TImesketch, then go to Charts > Histogram:\n",
        "\n",
        "**Histograms were part of the old UI and have not yet been ported back into the new UI. This picture is therefore from the old UI and needs to be updated.**\n",
        "\n",
        "![alt text](https://dfir.blog/content/images/2019/04/timesketch-histogram.png)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7zRjoel2wRdA"
      },
      "source": [
        "And here's how you'd do something similar in Python:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fxJN_YxSScJu"
      },
      "source": [
        "ts_results = ts_results.set_index('datetime')\n",
        "ts_results['2018':].message.resample('D').count().plot()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "okGUVLBvk4LN"
      },
      "source": [
        "Or to use grouping/aggregation in pandas"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "agA685Kpk624"
      },
      "source": [
        "ts_results.reset_index(inplace=True)\n",
        "ts_results['day'] = ts_results.datetime.dt.strftime('%Y%m%d')\n",
        "group = ts_results[['day', 'timestamp']].groupby('day', as_index=False)\n",
        "\n",
        "group_df = group.count().rename(columns={'timestamp': 'count'})\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iiM8mz0rleHD"
      },
      "source": [
        "group_df.sort_values('count', ascending=False)[:10]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Khe0KmZbyWl0"
      },
      "source": [
        "Okay, so from the graphs it looks like we have a good cluster at the end of February; let's look closer. I'll slice the results to only show after 2019-02-20:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "y-hGtMtYyrt6"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "\n",
        "date_chip = search.DateRangeChip()\n",
        "date_chip.start_time = '2019-02-25T00:00:00'\n",
        "date_chip.end_time = '2019-03-04T23:59:59'\n",
        "\n",
        "search_obj.query_string = 'TeamViewer'\n",
        "search_obj.add_chip(date_chip)\n",
        "search_obj.return_fields = '*'\n",
        "\n",
        "ts_results = search_obj.table\n",
        "#ts_results = ts_results.set_index('datetime')\n",
        "#ts_results['2019-02-20':][['timestamp_desc','data_type','filename','message']]\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rIQfr9vvmWqc"
      },
      "source": [
        "ts_results.data_type.value_counts()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2meDiywWmZxD"
      },
      "source": [
        "ts_results.search_string.value_counts()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QBDi0z_gmcmK"
      },
      "source": [
        "ts_results[ts_results.data_type.str.contains('chrome')][['datetime', 'url', 'domain', 'search_string', 'message', 'title']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XVDEnDw2mvGh"
      },
      "source": [
        "ts_results[ts_results.data_type == 'fs:stat'][['datetime', 'display_name', 'timestamp_desc']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-3krCPn50csr"
      },
      "source": [
        "So from this, in a short interval starting 2019-02-25T20:39, we can see:\n",
        "*  a Google search for \"teamviewer\"\n",
        "*  a visit in Chrome to teamviewer.com,\n",
        "*  then teamviewer.com/en-us/teamviewer-automatic-download/,\n",
        "*  and lastly a bunch of TeamViewer related files being created.\n",
        "\n",
        "The web browser and files created were done under the Administrator account (per the path filename), so that's our answer."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5EzZwgne_V44"
      },
      "source": [
        "### Q: How Many Times\n",
        "At least how many times did the teamviewer_desktop.exe run?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "--29x7kjADg3"
      },
      "source": [
        "Prefetch is a great artifact for \"how many times did something run\"-type questions, so let's look for Prefetch execution entries for the program in question:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "v2ZjHcsv_Voj"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "\n",
        "search_obj.query_string = 'data_type:\"windows:prefetch:execution\" AND teamviewer_desktop.exe'\n",
        "search_obj.return_fields = 'datetime,timestamp_desc,data_type,executable,run_count,message'\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','executable','run_count','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QwCaUO1TBLis"
      },
      "source": [
        "### Q: Execute Where\n",
        "After looking at the TEAMVIEWER_DESKTOP.EXE prefetch file, which path was the executable in at the time of execution?\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8gu7QiD-Bkou"
      },
      "source": [
        "We did all the work for this question with the previous query (the answer is in the message string), but we can explicitly query for the path:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Y1jC_-hMBLUG"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "\n",
        "search_obj.query_string = 'data_type:\"windows:prefetch:execution\" AND teamviewer_desktop.exe'\n",
        "search_obj.return_fields = '*'\n",
        "\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','executable','run_count', 'path_hints']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4Z5KJzwABK7V"
      },
      "source": [
        "## Registry Questions\n",
        "This last set of questions can be answered using the Windows Registry (and one from event logs)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oYn41EUYFf5O"
      },
      "source": [
        "Lots of registry questions depend on the Current Control Set, so let's verify what it is:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qbT0cf4dB_aJ"
      },
      "source": [
        "# Escaping fun: We need to esacpe the slashes in the key_path once for Timesketch and once for Python, so we'll have triple slashes (\\\\\\)\n",
        "\n",
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'data_type:\"windows:registry:key_value\" AND key_path:\"HKEY_LOCAL_MACHINE\\\\\\System\\\\\\Select\"'\n",
        "search_obj.return_fields='datetime,timestamp_desc,data_type,message'\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FoqCvGtlFnZ1"
      },
      "source": [
        "From the message, the Current control set is 1."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3QuYQux5FtlO"
      },
      "source": [
        "### Q: What was the timezone offset at the time of imaging? and What is the timezone of the Desktop"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sX5xYElTHxOh"
      },
      "source": [
        "I'm combining these, since the answer is in the same query:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f8CE4NYEFzWu"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "\n",
        "search_obj.query_string = 'data_type:\"windows:registry:timezone\"'\n",
        "search_obj.return_fields = 'datetime,timestamp_desc,data_type,message'\n",
        "ts_results = search_obj.table\n",
        "\n",
        "ts_results[['datetime','timestamp_desc','data_type','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HJDjeFc0bzu4"
      },
      "source": [
        "The message is really long; let's pull it out:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "s8UMrq0tb3u6"
      },
      "source": [
        "message = list(ts_results.message.unique())[0]\n",
        "buffer = []\n",
        "first = True\n",
        "key = ''\n",
        "for word in message.split():\n",
        "  if first:\n",
        "    print(word)\n",
        "    first = False\n",
        "    continue\n",
        "\n",
        "  if not word.endswith(':'):\n",
        "    buffer.append(word)\n",
        "    continue\n",
        "  \n",
        "  if key:\n",
        "    words = ' '.join(buffer)\n",
        "    buffer = []\n",
        "    print(f'{\" \"*4}{key} = {words}')\n",
        "\n",
        "  key = word[:-1]\n",
        "\n",
        "words = ' '.join(buffer)\n",
        "buffer = []\n",
        "print(f'{\" \"*4}{key} = {words}')\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NAloEuihHke2"
      },
      "source": [
        "The name of the Timezone is in the message string, as is the ActiveTimeBias, which we can use to get the UTC offset:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5R57xnzQGzEB"
      },
      "source": [
        "# The ActiveTimeBias is in minutes, so divide by -60 (I don't know why it's stored negative): \n",
        "420 / -60"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yU8rM-2fQC-g"
      },
      "source": [
        "### Q: When was the Windows OS installed?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yGnx4554QICl"
      },
      "source": [
        "Plaso actually parses this out as it's own data_type, so querying for it is easy:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_DTNDAiAMSgz"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "\n",
        "search_obj.query_string = 'data_type:\"windows:registry:installation\"'\n",
        "search_obj.return_fields = 'datetime,timestamp_desc,data_type,message'\n",
        "ts_results = search_obj.table\n",
        "\n",
        "ts_results[['datetime','timestamp_desc','data_type','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h6OG8tRbRf6b"
      },
      "source": [
        "### Q: What is the IP address of the Desktop?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kNmlBEFAR1lI"
      },
      "source": [
        "We already confirmed the Control Set is 001, so let's query for the registry key under that control set that holds the Interface information:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "angYvM_3RfA1"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string = 'key_path:\"System\\\\\\ControlSet001\\\\\\Services\\\\\\Tcpip\\\\\\Parameters\\\\\\Interfaces\"'\n",
        "search_obj.return_fields = 'datetime,timestamp_desc,data_type,message'\n",
        "\n",
        "ts_results = search_obj.table\n",
        "ts_results[['datetime','timestamp_desc','data_type','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LckTVA9eSDCR"
      },
      "source": [
        "There are a few entries, but only the last one has what we want. Reading through it (or using Ctrl+F) we can find the 'IPAddress' is 64.44.141.76."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "55kVv0nOcVKf"
      },
      "source": [
        "set(ts_results.message)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TstAJwDdpW8E"
      },
      "source": [
        "Or we can use str.extract:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0sS_9nFEpYTf"
      },
      "source": [
        "ts_results.message.str.extract(r'DhcpIPAddress: \\[REG_SZ\\] ([^ ]+)').drop_duplicates()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VXtkOaKTU5YP"
      },
      "source": [
        "### Q: Which User Shutdown Windows on February 25th 2019?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UbuECyqADgrp"
      },
      "source": [
        "Event logs seem like a good place to look for this answer, since a shutdown generates a 1074 event in the System event log. From the question, we have a fairly-narrow timeframe, so let's slice the results down to that after we do our query: "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6dq7IoM_U7Qo"
      },
      "source": [
        "search_obj = search.Search(ctf)\n",
        "search_obj.query_string =  'data_type:\"windows:evtx:record\" AND display_name:\"System.evtx\" AND event_identifier:\"1074\"'\n",
        "search_obj.return_fields='*'\n",
        "\n",
        "ts_results = search_obj.table\n",
        "ts_results = ts_results.set_index('datetime')\n",
        "ts_results['2019-02-25':'2019-02-26'][['timestamp_desc','data_type','username','message']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AlWim_NuGMeV"
      },
      "source": [
        "# Wrap Up"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BbHOAd3X9Sq-"
      },
      "source": [
        "That's it! Thanks for reading and I hope you found this useful. This walkthrough covered most of the questions from the 'Basic - Desktop' category; I may do other sections as well if there is time/interest. If you found this useful, check out Kristinn's demonstration of [Timesketch and Colab](https://colab.research.google.com/github/google/timesketch/blob/master/notebooks/colab-timesketch-demo.ipynb).\n",
        "\n",
        "You can get the free, open source tools I used to solve the CTF:\n",
        "* Plaso / Log2Timeline: https://github.com/log2timeline/plaso\n",
        "* Timesketch: https://github.com/google/timesketch\n",
        "* Colab(oratory): https://colab.sandbox.google.com/notebooks/welcome.ipynb "
      ]
    }
  ]
}
